'''
-- 创建 token 表
CREATE TABLE wx_tokens (
    id SERIAL PRIMARY KEY,
    user_id INT,
    token VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- token 有效期 12 小时
    expires_at TIMESTAMP DEFAULT NOW() + INTERVAL '12 hours',
    -- 添加外键约束，关联 users 表
    FOREIGN KEY (user_id) REFERENCES wx_users(id)
);

SELECT SETVAL('wx_tokens_id_seq',max(id),TRUE) FROM wx_tokens;

'''

import uuid
import datetime
import logging
import psycopg2


logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(name)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)
class WxTokenTableMethods: 

    def __init__(self, conn, cur):
        self.cursor = cur
        self.conn = conn

    # 生成token
    def generate_token_content(self):
        token = str(uuid.uuid4())
        return token

    # 插入token
    def insert_token(self, user_id):
        # 10年后过期
        expire_time = datetime.datetime.now() + datetime.timedelta(days=365*10)
        # 生成token
        token_content = self.generate_token_content()
        # 插入token
        sql = "INSERT INTO wx_tokens (user_id, token, expires_at) VALUES (%s, %s, %s)"
        values = (user_id, token_content, expire_time)
        self.cursor.execute(sql, values)
        logger.info(f"insert token success user_id: {user_id} token_content: {token_content}")

        return token_content

    # 查询最近的token
    def query_latest_token_by_user_id(self, user_id):
        
        sql = "SELECT token, created_at, expires_at FROM wx_tokens WHERE user_id = %s ORDER BY created_at DESC LIMIT 1"
        values = (user_id,)
        self.cursor.execute(sql, values)
        result = self.cursor.fetchone()
        if result:
            logger.info(f"query latest token success user_id: {user_id} token: {result['token']}")
            return result
        else:
            logger.info(f"query latest token failed user_id: {user_id} not found")
            return None
        
    # 验证token 也是 查询token
    def verify_token(self, token_content):
        
        sql = "SELECT user_id, created_at, expires_at FROM wx_tokens WHERE token = %s"
        values = (token_content,)
        self.cursor.execute(sql, values)
        result = self.cursor.fetchone()
        if result:
            # 验证token是否过期
            if result['expires_at'] > datetime.datetime.now():
                logger.info(f"verify token success token: {token_content} user_id: {result['user_id']}")
                return True, "token verified"
            else:
                logger.info(f"verify token failed token: {token_content} expired")
                return False, "token expired"
        else:
            logger.info(f"verify token failed token: {token_content} not found")
            return False, "token not found"
        